package edu.hku.c3330.hkucompanion.manager;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;

public class DataManager extends SQLiteOpenHelper
{
	private static Context context;
	private static DataManager dataManager;

	
	public static void closeDatabase()
	{
		DataManager.dataManager.close();
	}
	
	public static void setContext(Context context)
	{
		DataManager.context= context;
	}
	
	public static DataManager getInstance(Context context)
	{
		if (context != DataManager.context)
		{
			if (dataManager != null)
			{
				dataManager.close();
				dataManager = null;
			}
		}
		
		if (dataManager == null)
		{
			DataManager.dataManager = new DataManager(context);
			DataManager.context = context;
		}
		
		return dataManager;
	}
	
	public interface EventDbConstants extends BaseColumns
	{
		public static final String TABLE_NAME = "Events";
		public static final String 
		TITLE = "TITLE",
		DESC = "DESC",
		LOCATION = "LOCATION",
		IS_SINGLE = "IS_SINGLE",
		IS_CUSTOM = "IS_CUSTOM",
		PRIORITY = "PRIORITY",
		START = "START",
		END = "END";
	}
	
	public interface CourseDbConstants extends BaseColumns
	{
		public static final String TABLE_NAME = "Courses";
		public static final String 
		NAME = "NAME",
		DESC = "DESC",
		INSTRUCTOR = "INSTRUCTOR",
		COURSE_CODE = "COURSE_CODE";
	}
	
	public interface AssessmentDbConstants  extends BaseColumns
	{
		public static final String TABLE_NAME = "Assessments";
		public static final String 
		COURSE_ID = "COURSE_ID",
		EVENT_ID = "EVENT_ID",
		TYPE = "TYPE";
	}
	
	public interface ReferenceDbConstants  extends BaseColumns
	{
		public static final String TABLE_NAME = "AssessmentReferences";
		public static final String 
		ASSESSMENT_ID = "ASSESSMENT_ID",
		REFERENCE = "REF_CONTENT";
	}
	
	public interface RecurrenceDbConstants  extends BaseColumns
	{
		public static final String TABLE_NAME = "Recurrences";
		public static final String 
		EVENT_ID = "EVENT_ID",
		START_DATE = "START_DATE",
		END_DATE = "END_DATE",
		DURATION = "DURATION",
		DURATION_MONTH = "DURATION_MONTH",
		DURATION_YEAR = "DURATION_YEAR",
		PERIOD = "PERIOD",
		PERIOD_MONTH = "PERIOD_MONTH",
		PERIOD_YEAR = "PERIOD_YEAR";
	}
	
    private final static String DATABASE_NAME = "data.db";
    private final static int DATABASE_VERSION = 1;

    public DataManager(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    	//Create Events Table
    	createTables(db);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    	dropTables(db);
    	onCreate(db);
    }
    
    public static void createTables(SQLiteDatabase db)
    {
        String dbCreationString = 
        		"CREATE TABLE " + EventDbConstants.TABLE_NAME + " (" +
        		EventDbConstants._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + 
        		EventDbConstants.TITLE + " NVARCHAR, " + 
        		EventDbConstants.DESC + " NVARCHAR, " + 
        		EventDbConstants.LOCATION + " NVARCHAR, " + 
        		EventDbConstants.IS_SINGLE + " INTEGER, " + 
        		EventDbConstants.IS_CUSTOM + " INTEGER, " + 
        		EventDbConstants.PRIORITY + " INTEGER, " +
        		EventDbConstants.START + " INTEGER, " + 
        		EventDbConstants.END  + " INTEGER" +
        		");";
        db.execSQL(dbCreationString);
        
    	//Create Course Table
        dbCreationString = 
        		"CREATE TABLE " + CourseDbConstants.TABLE_NAME + " (" +
        		CourseDbConstants._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + 
        		CourseDbConstants.NAME + " NVARCHAR, " + 
        		CourseDbConstants.DESC + " NVARCHAR, " + 
        		CourseDbConstants.INSTRUCTOR + " NVARCHAR, " + 
        		CourseDbConstants.COURSE_CODE + " NVARCHAR" + 
        		");";
        db.execSQL(dbCreationString);
        
    	//Create Events Table
        dbCreationString = 
        		"CREATE TABLE " + AssessmentDbConstants.TABLE_NAME + " (" +
        		AssessmentDbConstants._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + 
        		AssessmentDbConstants.COURSE_ID + " INTEGER, " + 
        		AssessmentDbConstants.EVENT_ID + " INTEGER, " + 
        		AssessmentDbConstants.TYPE + " NVARCHAR" +
        		");";
        db.execSQL(dbCreationString);
        
        //Create Reference Table
        dbCreationString = 
        		"CREATE TABLE " + ReferenceDbConstants.TABLE_NAME + " (" +
        		ReferenceDbConstants._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + 
        		ReferenceDbConstants.ASSESSMENT_ID + " INTEGER, " + 
        		ReferenceDbConstants.REFERENCE + " NVARCHAR" + 
        		");";
        db.execSQL(dbCreationString);
                
    	//Create Recurrence Table
        dbCreationString = 
        		"CREATE TABLE " + RecurrenceDbConstants.TABLE_NAME + " (" +
        		RecurrenceDbConstants._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + 
        		RecurrenceDbConstants.EVENT_ID + " INTEGER, " + 
        		RecurrenceDbConstants.START_DATE + " INTEGER, " + 
        		RecurrenceDbConstants.END_DATE + " INTEGER, " + 
        		RecurrenceDbConstants.DURATION + " INTEGER, " + 
        		RecurrenceDbConstants.DURATION_MONTH + " INTEGER, " + 
        		RecurrenceDbConstants.DURATION_YEAR + " INTEGER, " + 
        		RecurrenceDbConstants.PERIOD + " INTEGER, " + 
        		RecurrenceDbConstants.PERIOD_MONTH + " INTEGER, " + 
        		RecurrenceDbConstants.PERIOD_YEAR + " INTEGER" + 
        		");";
        db.execSQL(dbCreationString);
    }
    
    
    public static void dropTables(SQLiteDatabase db)
    {
      	String dropTableString;
      	
      	dropTableString = "DROP TABLE IF EXISTS " + EventDbConstants.TABLE_NAME;
        db.execSQL(dropTableString);
        
        dropTableString = "DROP TABLE IF EXISTS " + CourseDbConstants.TABLE_NAME;
        db.execSQL(dropTableString);
        
        dropTableString = "DROP TABLE IF EXISTS " + AssessmentDbConstants.TABLE_NAME;
        db.execSQL(dropTableString);
        
        dropTableString = "DROP TABLE IF EXISTS " + ReferenceDbConstants.TABLE_NAME;
        db.execSQL(dropTableString);
        
        dropTableString = "DROP TABLE IF EXISTS " + RecurrenceDbConstants.TABLE_NAME;
        db.execSQL(dropTableString);
    }
    
    
}
